package com.helwen.tools.zhijiang;

import org.apache.poi.xwpf.usermodel.*;
import org.junit.jupiter.api.Test;

import java.io.*;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


/**
 * 从一个文件夹中读取word，  只是读取国标中的word固定文件。  拼成将xml格式参数转成json参数，去调东软件平台将枝江数据上传到宜昌平台中。
 * 生成建表sql 与xml形式  ,将文件夹中所有文档中的所有字段， 写到一个文件中去
 * @author Christmas_G
 *
 */
public class MultiToOneSql {
    @Test
    public void readWordCreateOneSql() throws FileNotFoundException, IOException {
        //XWPFDocument document = new XWPFDocument(new FileInputStream("C:\\develop\\docs\\枝江\\公共卫生\\重性精神疾病专案登记.docx"));
        //门急诊检查申请单.docx文档中有一个字段有空格health records已加下划线， 门(急)诊检查报告.doc文档中有个字段check_ position多一个空格。
        //住院入出院登记.docx文档中有两个outward_clerk字段。门急诊手术记录.docx有两个：blood_volumecode
        //XWPFDocument document = new XWPFDocument(new FileInputStream("D:\\全民健康\\医疗\\HIS及其他\\门急诊手术记录.docx"));
        /*String x="/controlActProcess/subject/registrationRequest/subject1/assignedEntity/id/item/@root";
        String[] arr=x.split("/");
        for(String s:arr){
            System.out.println(""+s);
        }*/
        try {
            //String filepath="C:\\develop\\docs\\枝江\\公共卫生";
            //String filepath="D:\\全民健康\\医疗\\HIS及其他";
            //String filepath="D:\\123\\全民健康\\医疗\\EMR";
            //String filepath="D:\\123\\全民健康\\医疗\\移动护理";
            String filepath="E:\\123\\全民健康\\医疗\\术前术后";
            File file=new File(filepath);//文件夹
            String[] filelist = file.list();
            File outfile=new File("E:\\全民健康\\建表sql3\\手术前后.txt");
            FileOutputStream fileOutputStream = new FileOutputStream(outfile);
            OutputStreamWriter osw = new OutputStreamWriter(fileOutputStream, "UTF-8");
            for (int ik = 0; ik < filelist.length; ik++) {
                String filename=filelist[ik];
                File readfile = new File(filepath + "\\" + filename);
                if(filename.startsWith("~")){
                    continue;
                }
                XWPFDocument document = new XWPFDocument(new FileInputStream(readfile));
                int index=filename.lastIndexOf(".");
                String textname=filename.substring(0,index)+".txt";
                // 获取word中的所有段落与表格
                List<IBodyElement> elements = document.getBodyElements();
                String protocol="";
                String[] arr={"tablename","table_name","tablename"};
                int j=0;
                for (IBodyElement element : elements) {
                    // 段落
                    if (element instanceof XWPFParagraph) {
                        String str= getTableCellText((XWPFParagraph) element);
                        if(str!=null && str.length()>1){
                            protocol=str;
                        }
                    }
                    // 表格
                    else if (element instanceof XWPFTable) {
                        String explaintable="";
                        if(protocol!=null && !protocol.equals("")){
                            if(protocol.indexOf("表名：")>0){
                                String[] farr=protocol.split("表名：");
                                protocol=farr[1];
                                explaintable=farr[0];
                            }else if(protocol.indexOf("表名:")>0){
                                String[] farr=protocol.split("表名:");
                                protocol=farr[1];
                                explaintable=farr[0];
                            }else if(protocol.indexOf("表:")>0){
                                String[] farr=protocol.split("表:");
                                protocol=farr[1];
                                explaintable=farr[0];
                            }else if(protocol.indexOf("表：")>0){
                                String[] farr=protocol.split("表：");
                                protocol=farr[1];
                                explaintable=farr[0];
                            }else if(protocol.indexOf("表")>0){
                                String[] farr=protocol.split("表");
                                protocol=farr[1];
                                explaintable=farr[0];
                            }
                        }
                        if(explaintable.indexOf("对应数据库")>0){
                            explaintable=explaintable.substring(0,explaintable.indexOf("对应数据库"));
                        }

                        //getTabelText((XWPFTable) element,textname,getRealField(protocol),getRealField(explaintable));
                        XWPFTable table=(XWPFTable) element;
                        List<XWPFTableRow> rows = table.getRows();
                        protocol=getRealField(protocol);
                        explaintable=getRealField(explaintable);
                        int x=0;
                        int ii=1;//字段类型所在列
                        int jj=3;//字段描述所在列
                        StringBuffer ceateSql=new StringBuffer("create table ").append(protocol+"(\r\n");
                        StringBuffer commonSql=new StringBuffer("");
                        StringBuffer xmlStr=new StringBuffer("");
                        StringBuffer jsonStr=new StringBuffer("");
                        Map<String,String> checkMp=new HashMap<>();
                        int size=rows.size();
                        int si=0;
                        for (XWPFTableRow row : rows) {
                            si++;
                            if(x==0){
                                List<XWPFTableCell> cells = row.getTableCells();
                                if(cells.size()>3){
                                    for(int i=1;i<4;i++){
                                        List<XWPFParagraph> paragraphType = cells.get(i).getParagraphs();//取字段类型，第2个
                                        String ftype=getTableCellText(paragraphType.get(0));//
                                        if(ftype.equals("字段描述") || ftype.equals("数据元名称") || ftype.equals("字段名称")){
                                            jj=i;
                                        }else if(ftype.indexOf("类型")>-1){
                                            ii=i;
                                        }
                                    }
                                    x++;

                                }
                                continue;
                            }
                            List<XWPFTableCell> cells = row.getTableCells();


                            String ycfield="";
                            if(cells.size()>6){
                                List<XWPFParagraph> ycpar = cells.get(6).getParagraphs();//取第7个单元格中的值
                                ycfield=getTableCellText(ycpar.get(0));//
                            }
                            //his中字段名称	字段描述	字段类型	必填， 有两种顺序
                            //检验中  字段名称	字段类型	必填	字段描述
                            //获取字段
                            List<XWPFParagraph> paragraphs = cells.get(0).getParagraphs();//取第一个单元格中的值
                            String path=getTableCellText(paragraphs.get(0));//
                            if(path==null || path.equals("")){
                                continue;
                            }else if(path.indexOf(" _")>0||path.indexOf("_ ")>0 || path.indexOf("-")>0){
                                System.out.println(filename+"带有空格字段："+path);
                                path=path.replace(" ", "");
                            }
                            List<XWPFParagraph> paragraphType = cells.get(ii).getParagraphs();//取字段类型，第2个
                            String ftype=getTableCellText(paragraphType.get(0));//
                            ftype=ftype.replace(" ", "");
                            if(ftype.indexOf("（")>0){
                                ftype=ftype.replace("（", "(");
                            }
                            if(ftype.indexOf("）")>0){
                                ftype=ftype.replace("）", ")");
                            }
                            ftype=ftype.trim();
                            if(checkMp.containsKey(path)){
                                System.out.println(filename+"有两个："+path);
                            }else {
                                checkMp.put(path,path);
                            }
                            if(ftype.equals("D8") || ftype.equalsIgnoreCase("DATE(8)")){
                                ftype="DATE";
                            }else if(ftype.equals("D14") || ftype.equalsIgnoreCase("DATE(14)")){
                                ftype="DATE";
                            }
                            String xmlpath=path.replace( " ",  "");
                            if(si==size){
                                ceateSql.append(path+" "+ftype+"\r\n");
                            }else{
                                ceateSql.append(path+" "+ftype+",\r\n");
                            }
                            //ceateSql.append(path+",");
                            //获取注释
                            List<XWPFParagraph> explans = cells.get(jj).getParagraphs();//取第4个单元格中的值。
                            String explain=getTableCellText(explans.get(0));//

                            commonSql.append("comment  on  column  ").append(protocol+".").append(path).append(" is '").append(explain).append("';\r\n");
                            //xmlStr.append("<!-- ").append(explain).append(" -->\r\n");
                            xmlStr.append("<").append(path).append("> </").append(path).append("> \r\n");
                            jsonStr.append("\"").append(ycfield).append("\" : \"${info.").append(path).append(" }\",\r\n");

                        }
                        osw.write(ceateSql.toString()+");\r\n");
                        osw.write("\r\n");
                        if(explaintable.indexOf("对应")>0){
                            explaintable=explaintable.split("对应")[0];
                        }
                        String tableex="COMMENT ON table "+protocol+" is '"+explaintable+"';\r\n";
                        osw.write(tableex);
                        osw.write(commonSql.toString());
                        osw.write("\r\n");
                        osw.write("\r\n");
                        osw.write("\r\n");



                        j++;
                    }
                }
                document.close();
            }
            osw.flush();
            osw.close();
            fileOutputStream.close();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    //去掉特殊字符
    private String getRealField(String str){
        if(str==null|| str.equals("")){
            return str;
        }
        String regEx="[`~!@#$%^&*()+=|{}':;',\\[\\].<>/?~！@#￥%……&*（）——+|{}【】‘；：”“’。，、？]";
        Pattern p = Pattern.compile(regEx);
        Matcher m = p.matcher(str);
        String s =m.replaceAll("").trim();
        return s;
    }
    /**
     * 获取段落内容
     *
     * @param paragraph
     */
    private void getParagraphText(XWPFParagraph paragraph) {
        // 获取段落中所有内容
        List<XWPFRun> runs = paragraph.getRuns();
        if (runs.size() == 0) {
            System.out.println("按了回车（新段落）222222");
            return;
        }
        StringBuffer runText = new StringBuffer();
        for (XWPFRun run : runs) {
            runText.append(run.text());
        }
        if (runText.length() > 0) {
            runText.append("，对齐方式222222：").append(paragraph.getAlignment().name());
            System.out.println(runText);
        }
    }

    private String getTableCellText(XWPFParagraph paragraph) {
        // 获取段落中所有内容
        List<XWPFRun> runs = paragraph.getRuns();
        if (runs.size() == 0) {
            //System.out.println("按了回车（新段落）");
            return "";
        }
        StringBuffer runText = new StringBuffer();
        for (XWPFRun run : runs) {
            runText.append(run.text());
        }
        //System.out.println(runText);

        return runText.toString();
    }

    /**
     * 获取表格内容
     *
     * @param table
     */
    private void getTabelText(XWPFTable table,String filename,String protocol,String tableExplain) throws Exception{


    }

    private String getSpecificationWord(String word){
        if(word.length()>1){
            return word.substring(0, 1).toUpperCase()+word.substring(1);
        }else {
            return word;
        }
    }
}
